import mysql.connector
import json

# 创建与数据库的连接
cnx = mysql.connector.connect(
    host="127.168.80.190",
    port=3306,
    user="root",
    password="12345678",
    database="gaokao",
    use_pure=True,
    ssl_disabled=True
)

# 创建一个游标对象
cursor = cnx.cursor()

# 定义要执行的 SQL 插入语句

with open('json/schoolDetail.json', 'r', encoding='utf-8') as f:
    data = json.load(f)
with open('json/schoolJobWhere.json', 'r', encoding='utf-8') as f:
    data_job = json.load(f)

for key, value in data_job.items():
    print(key)
    job_class = []
    job_ratio = []
    for key1, value1 in value.items():
        print(key1)
        print(value1)
        job_class.append(key1)
        job_ratio.append(value1)

    insert_query = "INSERT INTO schoolDetails (id, name, nature, level_name, f985, f211, dual_class, study, life, job, comprehensive, content, job_class, job_ratio, num_lab, num_subject, num_master, num_doctor, jobrate, postgraduate, abroad, men_rate) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
    insert_data = (key, data[key]['name'], data[key]['nature'], data[key]['level_name'], data[key]['f985'], 
                   data[key]['f211'], data[key]['dual_class'], data[key]['study'], data[key]['life'], data[key]['job'], 
                   data[key]['comprehensive'], data[key]['content'], str(job_class), str(job_ratio), data[key]['num_lab'], 
                   data[key]['num_subject'], data[key]['num_master'], data[key]['num_doctor'], data[key]['job_rate'], 
                   data[key]['postgraduate'], data[key]['abroad'], data[key]['men_rate'])
    cursor.execute(insert_query, insert_data)
    cnx.commit()

# 关闭游标和数据库连接
cursor.close()
cnx.close()

